import os
import re
import tkinter as tk
from tkinter import filedialog

def generate_validation_sql(update_statement):
    # 使用正则表达式解析更新语句
    pattern = re.compile(r'UPDATE\s+([a-zA-Z_]+)\s+SET\s+((?:\s*[a-zA-Z_]+\s*=\s*\S+\s*,\s*)+\s*[a-zA-Z_]+\s*=\s*\S+)\s+WHERE\s+([a-zA-Z_]+)\s*=\s*(\S+);', re.IGNORECASE | re.DOTALL)
    match = pattern.match(update_statement)

    if match:
        table_name = match.group(1)
        set_clause = match.group(2)
        where_column = match.group(3)
        where_value = match.group(4)

        # 生成验证 SQL
        validation_sql = f"SELECT * FROM {table_name} WHERE {where_column} = {where_value};"
        return validation_sql
    else:
        return "无法解析更新语句，请确保输入的是有效的更新语句。"

def read_sql_file(file_path):
    with open(file_path, 'r',encoding='UTF-8') as file:
        sql_content = file.read()
    return sql_content

def select_sql_file():
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口

    file_path = filedialog.askopenfilename(
        title="选择 SQL 文件",
        filetypes=[("SQL 文件", "*.sql"), ("所有文件", "*.*")],
    )

    return file_path

def append_to_file(file_path, content):
    with open(file_path, 'a',encoding='UTF-8') as file:
        file.write(content + "\n")  # 添加换行符

# 获取当前脚本所在目录
script_directory = os.path.dirname(os.path.abspath(__file__))

# 选择 SQL 文件
sql_file_path = select_sql_file()

if sql_file_path:
    # 获取验证 SQL 文件的路径
    validation_file_path = os.path.splitext(sql_file_path)[0] + "_validation.sql"

    # 从 SQL 文件中读取内容
    sql_content = read_sql_file(sql_file_path)

    # 使用正则表达式匹配 UPDATE 语句并生成验证 SQL
    for match in re.finditer(r'UPDATE\s+.*?;\s*', sql_content, re.IGNORECASE | re.DOTALL):
        update_statement = match.group()
        validation_sql = generate_validation_sql(update_statement)
        print("更新语句:", update_statement)
        print("验证 SQL:", validation_sql)

        # 将验证 SQL 内容追加到文件
        append_to_file(validation_file_path, validation_sql)
        print(f"验证 SQL 已保存到: {validation_file_path}\n")

else:
    print("用户取消选择文件。")
